package com.android.pos.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class POSDbAdapter {
	
	/* overall definition */
    private static final String DATABASE_NAME = "POS";
    private static final int DATABASE_VERSION = 1;
    private static final String DATABASE_TABLE_ITEM = "item_master";
    private static final String DATABASE_TABLE_TABLE = "table_master";
    private static final String DATABASE_TABLE_ORDER = "order_master";    
	
	/* item master table */
    public static final String KEY_ITEM = "item";
    public static final String KEY_PRICE = "price";
    public static final String KEY_ITEMROWID = "_id";
    
    /* table master table */    
    public static final String KEY_TABLE = "table_name";
    public static final String KEY_DESC = "desc";
    public static final String KEY_TABLEROWID = "_id"; 
    
    /* order master table */
    public static final String KEY_ORDERTABLE = "od_table";
    public static final String KEY_ORDERPRICE = "od_price";
    public static final String KEY_ORDERITEM = "od_item";
    public static final String KEY_ORDERDATE = "od_date";    
    public static final String KEY_ORDERROWID = "_id";
    
    /* Joined available table */
//    public static final String KEY_AVAILTABLE = "avail_table";
    
    /* Constant for join query */
    //private final String AVAILABLE_TABLES = "@string/available_table";    
    
    private DatabaseHelper mDbHelper;
    private SQLiteDatabase mDb;
    
    private static final String TAG = "POSDbAdapterItem";    
    
    /**
     * Database creation sql statement
     */
    // table for item
    private static final String DATABASE_CREATE_ITEM =
        "create table item_master (_id integer primary key autoincrement, "
        + "item text not null, price text not null);";
    
    // table for table which is in the restaurant
    private static final String DATABASE_CREATE_TABLE =
        "create table table_master (_id integer primary key autoincrement, "
        + "table_name text not null, desc text not null);";
    
    // table for order master
    private static final String DATABASE_CREATE_ORDER =
        "create table order_master (_id integer primary key autoincrement, "
        + "od_table text not null, od_price text not null,"
    	+ "od_item text not null, od_date text not null);";
    
    private final Context mCtx;
    
    private static class DatabaseHelper extends SQLiteOpenHelper {

        DatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            db.execSQL(DATABASE_CREATE_ITEM);
            db.execSQL(DATABASE_CREATE_TABLE);
            db.execSQL(DATABASE_CREATE_ORDER);            
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
                    + newVersion + ", which will destroy all old data");
            db.execSQL("DROP TABLE IF EXISTS item_master");            
            db.execSQL("DROP TABLE IF EXISTS table_master");
            db.execSQL("DROP TABLE IF EXISTS order_master");            
            onCreate(db);
        }
    }

    /**
     * Constructor - takes the context to allow the database to be
     * opened/created
     * 
     * @param ctx the Context within which to work
     */
    public POSDbAdapter(Context ctx) {
        this.mCtx = ctx;
    }

    /**
     * Open the notes database. If it cannot be opened, try to create a new
     * instance of the database. If it cannot be created, throw an exception to
     * signal the failure
     * 
     * @return this (self reference, allowing this to be chained in an
     *         initialization call)
     * @throws SQLException if the database could be neither opened or created
     */
    public POSDbAdapter open() throws SQLException {
    	Log.d(TAG, "Android is about to open DB...");
        mDbHelper = new DatabaseHelper(mCtx);
        mDb = mDbHelper.getWritableDatabase();
        return this;
    }

    public void close() {
        mDbHelper.close();
    }


    /**
	 * Create method for both item_master and table_master
     */
    public long createItem(String item, String price) {
        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_ITEM, item);
        initialValues.put(KEY_PRICE, price);
        
        Log.d(TAG, "First Item from POSDbAdapter is..." + item);
        Log.d(TAG, "Second Item from POSDbAdapter is..." + price);        

        Log.d(TAG, "createItem completed...");
        return mDb.insert(DATABASE_TABLE_ITEM, null, initialValues);
        
    }
    
    public long createTable(String table, String desc) {
        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_TABLE, table);
        initialValues.put(KEY_DESC, desc);

        Log.d(TAG, "createTable completed...");
        return mDb.insert(DATABASE_TABLE_TABLE, null, initialValues);
    }
    
    public long createOrder(String table, String item, String price, String date) {
        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_ORDERTABLE, table);
        initialValues.put(KEY_ORDERPRICE, price);
        initialValues.put(KEY_ORDERITEM, item);
        initialValues.put(KEY_ORDERDATE, date);        

        Log.d(TAG, "createOrder completed...");
        return mDb.insert(DATABASE_TABLE_ORDER, null, initialValues);
    }    

    /**
     * Delete the item/table with the given rowId
     * 
     * @param rowId id of note to delete
     * @return true if deleted, false otherwise
     */
    public boolean deleteItem(long rowId) {
    	Log.d(TAG, "Delete Item...");
        return mDb.delete(DATABASE_TABLE_ITEM, KEY_ITEMROWID + "=" + rowId, null) > 0;
    }
    
    public boolean deleteTable(long rowId) {
    	Log.d(TAG, "Delete Table...");
        return mDb.delete(DATABASE_TABLE_TABLE, KEY_TABLEROWID + "=" + rowId, null) > 0;
    }    
    
    public boolean deleteOrder(long rowId) {
    	Log.d(TAG, "Delete Order...");
        return mDb.delete(DATABASE_TABLE_ORDER, KEY_ORDERROWID + "=" + rowId, null) > 0;
    }        

    /**
     * Return a Cursor over the list of all items/tables in the database
     * 
     * @return Cursor over all notes
     */
    public Cursor fetchAllItems() {
    	Log.d(TAG, "Fetch All items...");
        return mDb.query(DATABASE_TABLE_ITEM, new String[] {KEY_ITEMROWID, KEY_ITEM,
        		KEY_PRICE}, null, null, null, null, null);
    }
    public Cursor fetchAllTables() {
    	Log.d(TAG, "Fetch All Tables...");
        return mDb.query(DATABASE_TABLE_TABLE, new String[] {KEY_TABLEROWID, KEY_TABLE,
        		KEY_DESC}, null, null, null, null, null);
    }
    public Cursor fetchAllOrders() {
    	Log.d(TAG, "Fetch All Orders...");
        return mDb.query(DATABASE_TABLE_ORDER, new String[] {KEY_ORDERROWID, KEY_ORDERTABLE,
        		KEY_ORDERPRICE, KEY_ORDERITEM, KEY_ORDERDATE}, null, null, null, null, null);
    }        
    public Cursor fetchAllAvailTables() {
    	
        StringBuffer sb = new StringBuffer();
        sb.append("select table_name from ");
        sb.append("(select a.table_name, b.od_table from table_master a ");
        sb.append("left outer join order_master b ");
        sb.append("on a._id = b.od_table) where od_table is null");
        
    	Log.d(TAG, "Fetch All Available Tables with query..." + sb.toString());        
        
    	
        return mDb.rawQuery(sb.toString(), null);
        
    }    
    
    /**
     * Return a Cursor positioned at the note that matches the given rowId
     * 
     * @param rowId id of note to retrieve
     * @return Cursor positioned to matching note, if found
     * @throws SQLException if note could not be found/retrieved
     */
    public Cursor fetchItem(long rowId) throws SQLException {

        Cursor mCursor =

            mDb.query(true, DATABASE_TABLE_ITEM, new String[] {KEY_ITEMROWID,
            		KEY_ITEM, KEY_PRICE}, KEY_ITEMROWID + "=" + rowId, null,
                    null, null, null, null);
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;

    }
    public Cursor fetchTables(long rowId) throws SQLException {

        Cursor mCursor =

            mDb.query(true, DATABASE_TABLE_TABLE, new String[] {KEY_TABLEROWID,
            		KEY_TABLE, KEY_DESC}, KEY_TABLEROWID + "=" + rowId, null,
                    null, null, null, null);
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;

    }    
    public Cursor fetchOrders(long rowId) throws SQLException {

        Cursor mCursor =

            mDb.query(true, DATABASE_TABLE_ORDER, new String[] {KEY_ORDERROWID,
            		KEY_ORDERTABLE, KEY_ORDERPRICE, KEY_ORDERITEM
            		, KEY_ORDERDATE}, KEY_ORDERROWID + "=" + rowId, null,
                    null, null, null, null);
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;

    }  

    /**
     * Update the note using the details provided. The note to be updated is
     * specified using the rowId, and it is altered to use the title and body
     * values passed in
     * 
     * @param rowId id of note to update
     * @param title value to set note title to
     * @param body value to set note body to
     * @return true if the note was successfully updated, false otherwise
     */
    public boolean updateItem(long rowId, String item, String price) {
        ContentValues args = new ContentValues();
        args.put(KEY_ITEM, item);
        args.put(KEY_PRICE, price);
        
        Log.d(TAG, "Update Note...");
        return mDb.update(DATABASE_TABLE_ITEM, args, KEY_ITEMROWID + "=" + rowId, null) > 0;
    }
    
    public boolean updateTable(long rowId, String table, String desc) {
        ContentValues args = new ContentValues();
        args.put(KEY_TABLE, table);
        args.put(KEY_DESC, desc);
        
        Log.d(TAG, "Update Table...");
        return mDb.update(DATABASE_TABLE_TABLE, args, KEY_TABLEROWID + "=" + rowId, null) > 0;
    }
    
    public boolean updateOrder(long rowId, String table, String item, String price, String date) {
        ContentValues args = new ContentValues();
        args.put(KEY_ORDERTABLE, table);
        args.put(KEY_ORDERPRICE, price);
        args.put(KEY_ORDERITEM, item);
        args.put(KEY_ORDERDATE, date);        
        
        Log.d(TAG, "Update Order...");
        return mDb.update(DATABASE_TABLE_ORDER, args, KEY_ORDERROWID + "=" + rowId, null) > 0;
    }	        

}
